package com.kx.yooli.kx.wechat.mapper.repayment;

import com.kx.yooli.kx.wechat.entity.nplm.OverdueCase;
import com.kx.yooli.kx.wechat.entity.repayment.ActiveRepayOperate;
import com.kx.yooli.kx.wechat.entity.repayment.RepaymentEntity;
import com.mybatisflex.annotation.UseDataSource;
import com.mybatisflex.core.BaseMapper;
import com.mybatisflex.core.query.QueryColumn;
import com.mybatisflex.core.query.QueryCondition;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface IRepaymentMapper extends BaseMapper<RepaymentEntity> {
    /**
     *
     * @param certNo select loanNo by param certNo
     * @return RepaymentEntity
     * @author sqwen
     */
    default RepaymentEntity selectLoanByCertNo(String certNo){
        QueryColumn queryColumn = new QueryColumn("kx_repayment","repayment_info", "cert_no");
        return   selectOneByCondition(QueryCondition.create(queryColumn,"=",certNo));
    }
    default List<RepaymentEntity> selectListLoanByCertNo(String certNo){
        QueryColumn queryColumn = new QueryColumn("kx_repayment","repayment_info", "cert_no");
        return   selectListByCondition(QueryCondition.create(queryColumn,"=",certNo));
    }

    default List<RepaymentEntity> selectListLoanByLoanNo(String loanNo){
        QueryColumn queryColumn = new QueryColumn("kx_repayment","repayment_info", "loan_no");
        return   selectListByCondition(QueryCondition.create(queryColumn,"=",loanNo));
    }
    @UseDataSource("ds2")
    @Select(" select  id as id,update_time as updateTime,cert_no as certNo,payment_type as paymentType,bank_no as bankNo,loan_no as loanNo ,result as result from kx_repayment.repayment_info" +
            " where loan_no=#{loanNo,jdbcType=VARCHAR}  and result=1 ")
    List<RepaymentEntity> selectLoanByAmountCertNo(String loanNo);

    @UseDataSource("ds2")
    @Select(" select cert_no as certNo,loan_no as loanNo ,result as result from kx_repayment.repayment_info" +
            " where cert_no=#{certNo,jdbcType=VARCHAR}  and result=1 GROUP BY cert_no,loan_no,result ")
    List<RepaymentEntity> selectLoanByAmountCertNoComm(String certNo);
    /*@UseDataSource("dh")
    @Select(" select c.LOAN_AMOUNT as loanAmount,o.SUCCESS_AMOUNT as successAmount,o.LOAN_NO as loanNo " +
            " from nplm.NPLM_ACTIVE_REPAY_OPERATE o inner join nplm.NPLM_LOAN_CONTRACT c on c.LOAN_NO=o.LOAN_NO" +
            " where o.LOAN_NO=#{loanNo,jdbcType=VARCHAR} and o.JS_RET_CODE='0000'  order by o.loan_no")
    List<ActiveRepayOperate> selectByCertNoActive(String loanNo);*/

    @UseDataSource("dh")
    @Select("SELECT DISTINCT n.indeed_total as successAmount,n.loan_no as loanNo,n.REPAY_DATE as finishTime,c.LOAN_AMOUNT as loanAmount,n.repay_status as contractStatus,a.ACCOUNT_NO as bankNo " +
            "FROM nplm.NPLM_REPAY_PLAN n " +
            "INNER JOIN  nplm.NPLM_LOAN_CONTRACT c ON c.LOAN_NO = n.LOAN_NO   " +
            "left  JOIN (" +
            "SELECT  ACCOUNT_NO,LOAN_NO,TO_CHAR(ACCEPT_TIME, 'YYYY-MM-DD') AS ACCEPT_TIME " +
            "FROM  nplm.NPLM_ACTIVE_REPAY_OPERATE WHERE LOAN_NO = #{loanNo,jdbcType=VARCHAR} AND JS_RET_CODE = '0000') a " +
            "ON n.LOAN_NO = a.LOAN_NO AND TO_CHAR(n.REPAY_DATE, 'YYYY-MM-DD') = a.ACCEPT_TIME " +
            "WHERE n.LOAN_NO = #{loanNo,jdbcType=VARCHAR} and  n.indeed_total <> 0")
    List<ActiveRepayOperate> selectByCertNoActive(String loanNo);
    @UseDataSource("ds2")
    @Select("select count(id) from repayment_info where cert_no=#{certNo,jdbcType=VARCHAR} and result=1 ")
    Integer selectCountByCertNo(String certNo);

    @UseDataSource("dh")
    @Select("select LOAN_NO as loanNo,max(PLANNED_TOTAL) as plannedTotal,max(REPAY_TIME) as repayTime,LOAN_AMOUNT as loanAmount from (" +
            "SELECT   c.LOAN_AMOUNT ,o.PLANNED_TOTAL ,o.CERT_NO ,o.LOAN_NO ,o.status ,o.REPAY_TIME  FROM nplm.NPLM_OVERDUE_CASE o " +
            "INNER JOIN nplm.NPLM_LOAN_CONTRACT c ON c.LOAN_NO = o.LOAN_NO  WHERE o.CERT_NO = #{certNo,jdbcType=VARCHAR}  GROUP BY " +
            " c.LOAN_AMOUNT,o.PLANNED_TOTAL,o.CERT_NO,o.LOAN_NO,o.REPAY_TIME ,o.status  ORDER BY o.UPDATE_TIME DESC  )GROUP BY LOAN_NO,LOAN_AMOUNT")
    List<OverdueCase> selectCaseByLoanNo(String certNo);
}
